\section{Stored procedures y Triggers}

\subsection{Queries}


 

\begin{lstlisting}[language=sql, breaklines=true]

/*CONSULTA 1 - SOLUCION CON NOT IN*/

SELECT pais.nombre FROM pais 
WHERE pais.id_pais NOT IN
(
	SELECT equipo.id_pais FROM jugador
	INNER JOIN integrante
		ON integrante.pasaporte = jugador.pasaporte
	INNER JOIN equipo
		ON integrante.id_equipo = equipo.id_equipo
	WHERE jugador.pasaporte NOT IN
	(
		SELECT participacion.pasaporte FROM participacion
		WHERE titularidad = 'T'
	)
)
AND pais.id_pais IN (SELECT id_pais FROM equipo)

--AND EXISTS(SELECT equipob.id_pais FROM equipo AS equipob WHERE equipob.id_pais = pais.id_pais)

/*CONSULTA 1 - SOLUCION CON NOT EXISTS*/
SELECT pais.nombre FROM equipo 
INNER JOIN pais
	ON pais.id_pais = equipo.id_pais
WHERE NOT EXISTS 
(
	SELECT integrante.pasaporte FROM integrante
	INNER JOIN jugador
		ON integrante.pasaporte = jugador.pasaporte
	WHERE NOT EXISTS
	(
		SELECT participacion.id_participacion FROM participacion
		WHERE participacion.id_equipo = equipo.id_equipo
		AND jugador.pasaporte = participacion.pasaporte			
		AND participacion.titularidad = 'T'
	)
	AND integrante.id_equipo = equipo.id_equipo
)

/*CONSULTA 2*/
SELECT integrante.nombre, cant_partidos, puntos, rebotes, asistencias FROM integrante,
	(SELECT jugador.pasaporte pasaporte, COUNT(*) cant_partidos, AVG(puntos) puntos, AVG(rebotes) rebotes, AVG(asistencias) asistencias FROM jugador 
		INNER JOIN participacion ON jugador.pasaporte = participacion.pasaporte
		INNER JOIN estadistica ON participacion.id_participacion = estadistica.id_participacion 
	GROUP BY jugador.pasaporte) subquery
WHERE integrante.pasaporte = subquery.pasaporte

/*PUNTO 3 SP QUE DEVUELVE ARBITROS CANDIDATOS*/
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'SP_ArbitrosCandidatos')
    DROP PROCEDURE SP_ArbitrosCandidatos
GO

CREATE PROCEDURE SP_ArbitrosCandidatos @idPartido INT 
AS 
DECLARE @idEquipoLoc AS INT
DECLARE @idEquipoVis AS INT
DECLARE @fechaPartido as DATETIME

SELECT TOP 1 @idEquipoLoc = id_equipo, @fechaPartido = partido.fecha FROM juega
	INNER JOIN partido
		ON juega.id_partido = partido.id_partido
WHERE partido.id_partido = @idPartido
ORDER BY id_equipo ASC

SELECT TOP 1 @idEquipoVis = id_equipo FROM juega
	INNER JOIN partido
		ON juega.id_partido = partido.id_partido
WHERE partido.id_partido = @idPartido
ORDER BY id_equipo DESC

-- Devuelvo los arbitros que no estan en alguna de las subconsultas con las restricciones pedidas
SELECT * FROM arbitro WHERE arbitro.id_arbitro NOT IN(
-- Traigo los arbitros que dirigieron al equipo local mas de una vez en partidos que gan\'o
SELECT D1 FROM (SELECT COUNT(*) Cant, dirige.id_arbitro D1
FROM juega J1, juega J2, equipo, pais, dirige
WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 > J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
AND J1.id_partido = J2.id_partido
AND J1.id_equipo = equipo.id_equipo
AND pais.id_pais = equipo.id_pais
AND J1.id_partido = dirige.id_partido
AND equipo.id_equipo = @idEquipoLoc
GROUP BY dirige.id_arbitro) SB
WHERE SB.Cant>1
UNION
-- Traigo los arbitros que dirigieron al equipo visitante mas de una vez en partidos que gan\'o
SELECT D1 FROM (SELECT COUNT(*) Cant, dirige.id_arbitro D1
FROM juega J1, juega J2, equipo, pais, dirige
WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 > J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
AND J1.id_partido = J2.id_partido
AND J1.id_equipo = equipo.id_equipo
AND pais.id_pais = equipo.id_pais
AND J1.id_partido = dirige.id_partido
AND equipo.id_equipo = @idEquipoVis
GROUP BY dirige.id_arbitro) SB
WHERE SB.Cant>1
UNION
-- Traigo los arbitros que dirigieron al equipo local mas de una vez en partidos que perdi\'o
SELECT D1 FROM (SELECT COUNT(*) Cant, dirige.id_arbitro D1
FROM juega J1, juega J2, equipo, pais, dirige
WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 < J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
AND J1.id_partido = J2.id_partido
AND J1.id_equipo = equipo.id_equipo
AND pais.id_pais = equipo.id_pais
AND J1.id_partido = dirige.id_partido
AND equipo.id_equipo = @idEquipoLoc
GROUP BY dirige.id_arbitro) SB
WHERE SB.Cant>1
UNION
-- Traigo los arbitros que dirigieron al equipo visitante mas de una vez en partidos que perdi\'o
SELECT D1 FROM (SELECT COUNT(*) Cant, dirige.id_arbitro D1
FROM juega J1, juega J2, equipo, pais, dirige
WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 < J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
AND J1.id_partido = J2.id_partido
AND J1.id_equipo = equipo.id_equipo
AND pais.id_pais = equipo.id_pais
AND J1.id_partido = dirige.id_partido
AND equipo.id_equipo = @idEquipoVis
GROUP BY dirige.id_arbitro) SB
WHERE SB.Cant>1
UNION
-- Traigo los arbitros que dirigieron al equipo local cuando gan\'o y al visitante cuando perdi\'o
SELECT DISTINCT SQ1.D1 FROM (
SELECT dirige.id_arbitro D1
FROM juega J1, juega J2, equipo, pais, dirige
WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 > J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
AND J1.id_partido = J2.id_partido
AND J1.id_equipo = equipo.id_equipo
AND pais.id_pais = equipo.id_pais
AND J1.id_partido = dirige.id_partido
AND equipo.id_equipo = @idEquipoLoc
AND dirige.id_arbitro NOT IN(
	SELECT dirige.id_arbitro D1
	FROM juega J1, juega J2, equipo, pais, dirige
	WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 <> J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
	AND J1.id_partido = J2.id_partido
	AND J1.id_equipo = equipo.id_equipo
	AND pais.id_pais = equipo.id_pais
	AND J1.id_partido = dirige.id_partido
	AND equipo.id_equipo = @idEquipoLoc
	GROUP BY dirige.id_arbitro
	HAVING COUNT(*) > 1)
) SQ1,
(SELECT dirige.id_arbitro D1
FROM juega J1, juega J2, equipo, pais, dirige
WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 < J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
AND J1.id_partido = J2.id_partido
AND J1.id_equipo = equipo.id_equipo
AND pais.id_pais = equipo.id_pais
AND J1.id_partido = dirige.id_partido
AND equipo.id_equipo = @idEquipoVis
AND dirige.id_arbitro NOT IN(
	SELECT dirige.id_arbitro D1
	FROM juega J1, juega J2, equipo, pais, dirige
	WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 <> J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
	AND J1.id_partido = J2.id_partido
	AND J1.id_equipo = equipo.id_equipo
	AND pais.id_pais = equipo.id_pais
	AND J1.id_partido = dirige.id_partido
	AND equipo.id_equipo = @idEquipoVis
	GROUP BY dirige.id_arbitro
	HAVING COUNT(*) > 1)
) SQ2
WHERE SQ1.D1 = SQ2.D1
UNION
-- Traigo los arbitros que dirigieron al equipo local cuando perdi\'o y al visitante cuando gan\'o
SELECT SQ1.D1 FROM (
SELECT dirige.id_arbitro D1
FROM juega J1, juega J2, equipo, pais, dirige
WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 < J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
AND J1.id_partido = J2.id_partido
AND J1.id_equipo = equipo.id_equipo
AND pais.id_pais = equipo.id_pais
AND J1.id_partido = dirige.id_partido
AND equipo.id_equipo = @idEquipoLoc
AND dirige.id_arbitro NOT IN(
	SELECT dirige.id_arbitro D1
	FROM juega J1, juega J2, equipo, pais, dirige
	WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 <> J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
	AND J1.id_partido = J2.id_partido
	AND J1.id_equipo = equipo.id_equipo
	AND pais.id_pais = equipo.id_pais
	AND J1.id_partido = dirige.id_partido
	AND equipo.id_equipo = @idEquipoLoc
	GROUP BY dirige.id_arbitro
	HAVING COUNT(*) > 1)
) SQ1,
(SELECT dirige.id_arbitro D1
FROM juega J1, juega J2, equipo, pais, dirige
WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 > J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
AND J1.id_partido = J2.id_partido
AND J1.id_equipo = equipo.id_equipo
AND pais.id_pais = equipo.id_pais
AND J1.id_partido = dirige.id_partido
AND equipo.id_equipo = @idEquipoVis
AND dirige.id_arbitro NOT IN(
	SELECT dirige.id_arbitro D1
	FROM juega J1, juega J2, equipo, pais, dirige
	WHERE J1.puntaje1+J1.puntaje2+J1.puntaje3+J1.puntaje4 <> J2.puntaje1+J2.puntaje2+J2.puntaje3+J2.puntaje4
	AND J1.id_partido = J2.id_partido
	AND J1.id_equipo = equipo.id_equipo
	AND pais.id_pais = equipo.id_pais
	AND J1.id_partido = dirige.id_partido
	AND equipo.id_equipo = @idEquipoVis
	GROUP BY dirige.id_arbitro
	HAVING COUNT(*) > 1)
) SQ2
WHERE SQ1.D1 = SQ2.D1
UNION
--traigo los \'arbitros que dirigen partidos a la misma hora que el partido pasado por par\'ametro
SELECT D1.id_arbitro FROM dirige D1, partido
WHERE D1.id_partido = partido.id_partido
AND partido.fecha = @fechaPartido
--AND partido.id_partido <> @idPartido
)
GO

/*PUNTO 4: CONSTRAINT SOBRE DIRIGE*/
--Elimino la constraint
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[CHECK_PaisArbitro]') AND OBJECTPROPERTY(id, N'IsCheckCnst') = 1) 
	ALTER TABLE dirige DROP CONSTRAINT CHECK_PaisArbitro
GO
--Elimino la funci\'on
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'CoincidenPaisEquipoArbitro') AND xtype = N'FN')
	DROP FUNCTION CoincidenPaisEquipoArbitro
GO

CREATE FUNCTION CoincidenPaisEquipoArbitro(@idArbitro INT, @idPartido INT)
RETURNS INT
AS
BEGIN
	DECLARE @res INT
	SELECT @res = COUNT(*) FROM pais
		INNER JOIN arbitro
			ON pais.id_pais = arbitro.id_pais		
	WHERE pais.id_pais IN
	(
		SELECT P2.id_pais FROM partido
			INNER JOIN juega
				ON partido.id_partido = juega.id_partido
		INNER JOIN equipo
			ON juega.id_equipo = equipo.id_equipo
		INNER JOIN pais P2
			ON P2.id_pais = equipo.id_pais
		WHERE partido.id_partido = @idPartido
	)
	AND arbitro.id_arbitro = @idArbitro	
	RETURN(@res)
END
GO

ALTER TABLE dirige ADD CONSTRAINT CHECK_PaisArbitro
    CHECK (dbo.CoincidenPaisEquipoArbitro(id_Arbitro, id_Partido)= 0)
    
/*PUNTO 5: TRIGGER QUE LEVANTA UN ERROR SI SE INTENTA CREAR UN PARTIDO CON MAS DE 2 EQUIPOS*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChequeaCantEquiposPartido]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
	DROP TRIGGER [dbo].[ChequeaCantEquiposPartido]
GO

CREATE TRIGGER ChequeaCantEquiposPartido
ON juega
FOR INSERT
AS
	IF (SELECT COUNT(*) FROM juega 
	INNER JOIN INSERTED I
		ON I.id_partido = juega.id_partido) > 2
    BEGIN
		RAISERROR 50001 'Un partido no puede ser jugado por m\'as de 2 equipos'
		ROLLBACK TRANSACTION
		RETURN
    END
GO

--Elimino la constraint
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[CHECK_CantEquipos]') AND OBJECTPROPERTY(id, N'IsCheckCnst') = 1) 
	ALTER TABLE equipo DROP CONSTRAINT CHECK_CantEquipos
GO
--Elimino la funci\'on
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'equipos') AND xtype = N'FN')
	DROP FUNCTION equipos
GO

CREATE FUNCTION equipos()
RETURNS INT
AS
BEGIN
	DECLARE @res INT
	SELECT DISTINCT @res = COUNT(*) FROM equipo
	RETURN(@res)
END
GO

ALTER TABLE equipo ADD CONSTRAINT CHECK_CantEquipos
	CHECK (dbo.equipos() = 8)
\end{lstlisting}


